package com.lewei.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.lewei.model.PrintInModel;
import com.lewei.model.CertificateInfo;



/*
 * 
 * 所需jar包
 * 下载地址http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203
 * .zip <br />
 * poi-3.7-20101029.jar poi-ooxml-3.7-20101029.jar<br />
 * poi-ooxml-schemas-3.7-20101029.jar xmlbeans-2.3.0.jar dom4j-1.6.1.jar
 * 
 * 
 */

/**
 * Excel操作类。
 * Excel导入证书数据
 */
public class ExcelCertificateNo {

	/** 六位年月日 */
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	/** excel数据列 */
	// private static List<int[]> dataColum = new ArrayList<int[]>();
	// static {
	// // AA.AP
	// dataColum.add(new int[] { 0, 1, 11, 12, 14 });
	// // AMD
	// dataColum.add(new int[] { 0, 1, 10, 11, 13 });
	// // 35100
	// dataColum.add(new int[] { 0, 1, 9, 10, 19 });
	// // 35200
	// dataColum.add(new int[] { 0, 1, 9, 10, 19 });
	// // 35300
	// dataColum.add(new int[] { 0, 1, 8, 9, 12 });
	// // AMDDOP门机
	// dataColum.add(new int[] { 1, 4, 0, 8, 13 });
	// }

	/** 禁止实例化 */
	private ExcelCertificateNo() {
	};

	public static void main(String[] args) {
		
		File file = new File("C:\\toolsZ\\labelPrint\\config\\certificateNo.xlsx");
		try {
			List<CertificateInfo> list = readExcel(file);
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i).getExpireDate());
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 对外提供读取excel 的方法
	 */
	public static List<CertificateInfo> readExcel(File file) throws IOException {
		String fileName = file.getName();

		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
		if ("xls".equals(extension)) {
			return read2003Excel(file);
		} else if ("xlsx".equals(extension)) {
			return read2007Excel(file);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	@SuppressWarnings("deprecation")
	private static List<CertificateInfo> read2003Excel(File file) throws IOException {
		List<CertificateInfo> list = new ArrayList<CertificateInfo>();
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = hwb.getSheetAt(0);
		HSSFRow row = null;
		HSSFCell cell = null;
		for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (cell == null || cell.getStringCellValue().trim().equals("")) {
				continue;
			}

			// String sheetName = sheet.getSheetName();

			CertificateInfo CertificateInfo=new CertificateInfo();
			
			String str = "";
			// 鋼絲繩
			cell = row.getCell(0);
			cell.setCellType(CellType.STRING);
			str = cell.getStringCellValue().trim();
			CertificateInfo.setLineThickness(str);
			//型號
			cell = row.getCell(1);
			cell.setCellType(CellType.STRING);
			str = cell.getStringCellValue().trim();
			CertificateInfo.setModelNo(str);
			//額定速度
			cell = row.getCell(2);
			cell.setCellType(CellType.STRING);
			str = cell.getStringCellValue().trim();
			CertificateInfo.setVns(str);
			//證書編號
			cell = row.getCell(4);
			cell.setCellType(CellType.STRING);
			str = cell.getStringCellValue().trim();
			CertificateInfo.setCertificateNo(str);

			//實驗單位
			cell = row.getCell(5);
			cell.setCellType(CellType.STRING);
			str = cell.getStringCellValue().trim();
			CertificateInfo.setTestingAgency(str);
			
			//證書到期時間
			cell = row.getCell(6);
			if (cell == null) {
				continue;
			}
			cell.setCellType(CellType.NUMERIC);
			str = sdf.format(cell.getDateCellValue());
			CertificateInfo.setExpireDate(str);
			
		
			list.add(CertificateInfo);
		}
		hwb.close();
		return list;
	}

	/**
	 * 读取Office 2007 excel
	 */
	@SuppressWarnings("deprecation")
	private static List<CertificateInfo> read2007Excel(File file){
		try {
			List<CertificateInfo> list = new ArrayList<CertificateInfo>();
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
			// 读取第一章表格内容
			XSSFSheet sheet = xwb.getSheetAt(0);
			XSSFRow row = null;
			XSSFCell cell = null;
			for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				CertificateInfo CertificateInfo=new CertificateInfo();
				

				String str = "";
				// 鋼絲繩
				cell = row.getCell(0);
				cell.setCellType(CellType.STRING);
				str = cell.getStringCellValue().trim();
				CertificateInfo.setLineThickness(str);
				//型號
				cell = row.getCell(1);
				cell.setCellType(CellType.STRING);
				str = cell.getStringCellValue().trim();
				CertificateInfo.setModelNo(str);
				//額定速度
				cell = row.getCell(2);
				cell.setCellType(CellType.STRING);
				str = cell.getStringCellValue().trim();
				CertificateInfo.setVns(str);
				//證書編號
				cell = row.getCell(4);
				cell.setCellType(CellType.STRING);
				str = cell.getStringCellValue().trim();
				CertificateInfo.setCertificateNo(str);

				//實驗單位
				cell = row.getCell(5);
				cell.setCellType(CellType.STRING);
				str = cell.getStringCellValue().trim();
				CertificateInfo.setTestingAgency(str);
				
				//證書到期時間
				cell = row.getCell(6);
				if (cell == null) {
					continue;
				}
				cell.setCellType(CellType.NUMERIC);
				str = sdf.format(cell.getDateCellValue());
				CertificateInfo.setExpireDate(str);
				
				list.add(CertificateInfo);
			}
			xwb.close();
			return list;
		} catch (NumberFormatException e) {
			e.printStackTrace();
			throw new RuntimeException("excel数据格式错误！");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("找不到文件！");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("找不到文件！");
		}
	}
}